package data.news.db;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.TreeMap;

import common.Common;
import data.news.Article;
import data.news.ArticleIterator;

import weeny.util.logs.Debug;


public class NewsDB {

	/** The DB and table name in which all the news article data are stored */
	public static String  DB_NAME       = "skiena";
	public static String  TABLE_NAME    = "text";

	/** every entry in the table has 9 field */
	public static String  FLD_ID        = "id";
	public static String  FLD_URL       = "url";
	public static String  FLD_DATE      = "date";
	public static String  FLD_TITLE     = "title";
	public static String  FLD_SRC_NAME  = "sourcename";
	public static String  FLD_ART_TYPE  = "articletype";
	public static String  FLD_SYN_DICT  = "syndicate";
	public static String  FLD_PRE_TYPE  = "predictedarticletype";
	public static String  FLD_CONTENT   = "content";
	
	/** the statement to select a record based on id */
	public static String  SELECT_BY_ID  = "SELECT * from "+TABLE_NAME+" where id=?";

	
	private static String  DB_CONNECTION = "jdbc:mysql://localhost/"+DB_NAME;
	private static Connection conn = null;
	public static void closeConn(){
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static Connection connection2DB(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null && !conn.isClosed()) {
				return conn;
			}
			conn =  DriverManager.getConnection(DB_CONNECTION, "root", null);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	private static PreparedStatement insertArticle(Connection conn, Article art) throws SQLException{
		String insert = "INSERT into text " +
        "("+FLD_ID+","+FLD_URL+","+FLD_DATE+","+FLD_TITLE+","+FLD_SRC_NAME+","+FLD_ART_TYPE+","
           +FLD_SYN_DICT+","+FLD_PRE_TYPE+","+FLD_CONTENT+") "+
        "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement prep = conn.prepareStatement(insert);
		prep.setLong  (1, art.getArticleID());
		prep.setString(2, art.getUrl());
		prep.setInt   (3, art.getDate());
		prep.setString(4, art.getTitle());
		prep.setString(5, art.getSourcename());
		prep.setString(6, art.getArticletype());
		prep.setString(7, art.getSyndicate());
		prep.setString(8, art.getPredictedarticletype());
		prep.setString(9, art.getContent());
		return prep;
	}
	
	public static void dumpAllData2DB() throws SQLException, IOException {
		String fileName = Common.originalFile(5);
		ArticleIterator it = new ArticleIterator(new BufferedReader(new FileReader(fileName)));
		Connection conn = connection2DB();
		Debug.SHOW_LINE_NUM = false;
		while (it.hasNext()){
			Article art = it.next();
			boolean alreadyThere = article(conn, art.getArticleID()) != null;
			if (alreadyThere) {
				Debug.tag(0, "SKIP", "ID = "+art.getArticleID());
			}
			else{
				PreparedStatement insert = insertArticle(conn, art);
				insert.execute();
				Debug.tag(0, " ADD", "ID = "+art.getArticleID()+"\t content size = "+art.getContent().length());
				insert.close();
			}
			emptyCache();
		}
		conn.close();
	}
	
	
	private static Map<Long, Article> CACHE = new TreeMap<Long, Article>();
	public static void emptyCache(){
		CACHE.clear();
	}
	public static Article cacheArticle(long id){
		Connection conn = NewsDB.connection2DB();
		Article result = article(conn, id);
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public static Article article(long id){
		Connection conn = NewsDB.connection2DB();
		Article result = article(conn, id);
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		emptyCache();
		return result;
	}
	public static Article article(Connection conn, long id){
		if (CACHE.containsKey(id)) {
			return CACHE.get(id);
		}
		try {
			PreparedStatement stmt = conn.prepareStatement(NewsDB.SELECT_BY_ID);
			stmt.setLong(1, id);
			ResultSet rs = stmt.executeQuery();
			Article article = new Article(id);
			if (!rs.next()) {
				rs.close();
				return null;
			}
			article.setUrl(rs.getString(NewsDB.FLD_URL));
			article.setTitle(rs.getString(NewsDB.FLD_TITLE));
			article.setDate(rs.getInt(NewsDB.FLD_DATE));
			article.setSourcename(rs.getString(NewsDB.FLD_SRC_NAME));
			article.setArticletype(rs.getString(NewsDB.FLD_ART_TYPE));
			article.setSyndicate(rs.getString(NewsDB.FLD_SYN_DICT));
			article.setPredictedarticletype(rs.getString(NewsDB.FLD_PRE_TYPE));
			article.setContent(rs.getString(NewsDB.FLD_CONTENT));
			rs.close();
			stmt.close();
			CACHE.put(id, article);
			return article;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}